package www.worq.model.dao;

import java.sql.SQLException;
import java.util.ArrayList;

import www.worq.controller.beans.notariaBean;

public class notariasDao extends oreqDao {

	public ArrayList<notariaBean> getNOTARIAS() throws SQLException {
		ArrayList<notariaBean> cat_notarias = new ArrayList<notariaBean>();
		try {
			StringBuilder query = new StringBuilder();
			query.append("Select * from cat_notaria");
			conn = this.getConnectionMYSQL();
			ps = conn.prepareStatement(query.toString());
			rs = ps.executeQuery();
			System.out.println("dao.notarias.getNOTARIAS.Ejecutado----------> "
					+ query.toString());

			while (rs.next()) {
				notariaBean oficina = new notariaBean();
				oficina.setId_notaria(rs.getInt(1));
				oficina.setNombre(rs.getString(2));
				oficina.setTitular(rs.getString(3));
				oficina.setDireccion(rs.getString(4));
				oficina.setHrs_atencion(rs.getString(5));
				cat_notarias.add(oficina);
			}
			System.out.println("dao.notarias.getNOTARIAS----------> Cargado");
		} catch (Exception e) {
			System.out.println("notariasDao...Error..." + e.getMessage());
		} finally {
			close(conn);
			close(ps);
			close(rs);
		}
		return cat_notarias;
	}

	public int nextId() {
		int id = 0;
		try {
			StringBuilder query = new StringBuilder();
			query.append("Select id_notaria from cat_notaria");
			conn = this.getConnectionMYSQL();
			ps = conn.prepareStatement(query.toString());
			rs = ps.executeQuery();
			System.out.println("dao.notarias.nextId.Ejecutado----------> "
					+ query.toString());

			while (rs.next()) {
				if (id < rs.getInt(1)) {
					id = rs.getInt(1);
				}
			}
			System.out.println("dao.notarias.nextId----------> Cargado");
		} catch (Exception e) {
			System.out.println("notariasDao...Error..." + e.getMessage());
		} finally {
			close(conn);
			close(ps);
			close(rs);
		}
		return id + 1;
	}

	public void insertNOTARIA(notariaBean bean) throws Exception {
		try {
			StringBuilder query = new StringBuilder();
			conn = this.getConnectionMYSQL();

			query.append("INSERT INTO cat_notaria ")
					.append("(id_notaria, nombre, titular, direccion, hrs_atencion) ")
					.append("values ('").append(bean.getId_notaria())
					.append("','").append(bean.getNombre()).append("','")
					.append(bean.getTitular()).append("','")
					.append(bean.getDireccion()).append("','")
					.append(bean.getHrs_atencion()).append("');");
			System.out
					.println("dao.notarias.insertNOTARIA.Ejecutado----------> "
							+ query.toString());
			st = conn.createStatement();
			st.executeUpdate(query.toString());

			System.out
					.println("dao.notarias.insertNOTARIA----------> insertado");

		} catch (SQLException error) {
			throw new Exception(error.getMessage());
		} catch (Exception error) {
			throw new Exception(error.getMessage());
		} finally {
			close(conn);
			close(st);
		}
	}

	public void deleteNOTARIA(notariaBean bean) throws Exception {
		try {
			StringBuilder query = new StringBuilder();
			conn = this.getConnectionMYSQL();

			query.append("DELETE FROM cat_notaria where id_notaria=").append(
					bean.getId_notaria());

			st = conn.createStatement();
			st.executeUpdate(query.toString());
			System.out
					.println("dao.notarias.deleteNOTARIA.Ejecutado----------> "
							+ query.toString());
			System.out
					.println("dao.notarias.deleteNOTARIA----------> eliminado");

		} catch (SQLException error) {
			throw new Exception(error.getMessage());
		} catch (Exception error) {
			throw new Exception(error.getMessage());
		} finally {
			close(conn);
			close(st);
		}
	}

	public void updateNOTARIA(notariaBean bean) throws Exception {
		try {
			StringBuilder query = new StringBuilder();
			conn = this.getConnectionMYSQL();

			query.append("UPDATE cat_notaria SET ").append("nombre='")
					.append(bean.getNombre()).append("',titular='")
					.append(bean.getTitular()).append("',direccion='")
					.append(bean.getDireccion()).append("',hrs_atencion='")
					.append(bean.getHrs_atencion())
					.append("' where id_notaria=").append(bean.getId_notaria())
					.append(";");

			st = conn.createStatement();
			st.executeUpdate(query.toString());
			System.out
					.println("dao.notarias.updateNOTARIA.Ejecutado----------> "
							+ query.toString());
			System.out
					.println("dao.notarias.updateNOTARIA----------> actualizado");

		} catch (SQLException error) {
			throw new Exception(error.getMessage());
		} catch (Exception error) {
			throw new Exception(error.getMessage());
		} finally {
			close(conn);
			close(st);
		}
	}
}
